package up7.database.sql;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import com.google.gson.Gson;

import up7.database.DbHelper;
import up7.database.mysql.MySQLFile;
import up7.database.oracle.OracleFile;
import up7.model.FileInf;
import up7.sql.*;
import up7.utils.ConfigReader;

/*
 * 原型
*/
public class SqlFile {

	public static SqlFile build(){
		DataBaseType dt = ConfigReader.dbType();
		if( dt == DataBaseType.MySQL ) return new MySQLFile();
		else if( dt == DataBaseType.Oracle||
				dt == DataBaseType.DMDB) return new OracleFile();
		//else if( dt == DataBaseType.KingBase ) return new DBFileOdbc();
		else return new SqlFile();
	}

	public SqlFile()
	{
	}

	SqlTable table()
	{
		return  SqlTable.build("up7_files");
	}

	public static boolean existSameFile(String n,String pid,String uid) 
	{
		SqlWhereMerge swm = new SqlWhereMerge();
		swm.equal("f_nameLoc", n);
		swm.equal("f_pid", pid);
		swm.equal("f_deleted", 0);
		swm.equal("f_uid", uid);
		
		SqlExec se = new SqlExec();
		return se.count("up7_files", swm.to_sql())>0;
	}

	/**
	 * 清空文件表，文件夹表数据。
	 */
	public void Clear()
	{
		DbHelper db = new DbHelper();
		db.ExecuteNonQuery("delete from up7_files");
		db.ExecuteNonQuery("delete from up7_folders");
	}
	
	public void merged(String id)
	{
		String sql = "update up7_files set f_lenSvr=f_lenLoc,f_perSvr='100%',f_merged=1 where f_id=?";
		DbHelper db = new DbHelper();
		PreparedStatement cmd = db.GetCommand(sql);
		
		try
		{
			cmd.setString(1,id);
		} catch(SQLException e){
			e.printStackTrace();
		}
		db.ExecuteNonQuery(cmd);
	}
	
	public void add(FileInf inf)
	{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into up7_files(");
		sb.append(" f_id");//1
		sb.append(",f_fdTask");//2
		sb.append(",f_uid");//3
		sb.append(",f_nameLoc");//4
		sb.append(",f_nameSvr");//5
		sb.append(",f_pathLoc");//6
		sb.append(",f_pathSvr");//7
		sb.append(",f_pathRel");//8
		sb.append(",f_blockCount");//9
		sb.append(",f_blockSize");//10
		sb.append(",f_blockPath");//11
		sb.append(",f_lenLoc");//12
		sb.append(",f_sizeLoc");//13
		sb.append(",f_pid");//14
		sb.append(",f_fdChild");//15
		
		sb.append(") values(");
				
		sb.append(" ?");//sb.append("@f_id");
		sb.append(",?");//sb.append(",@f_fdTask");
		sb.append(",?");//sb.append(",@f_uid");
		sb.append(",?");//sb.append(",@f_nameLoc");
		sb.append(",?");//sb.append(",@f_nameSvr");
		sb.append(",?");//sb.append(",@f_pathLoc");
		sb.append(",?");//sb.append(",@f_pathSvr");
		sb.append(",?");//sb.append(",@f_pathRel");
		sb.append(",?");//sb.append(",@f_blockCount");
		sb.append(",?");//sb.append(",@f_blockSize");
		sb.append(",?");//sb.append(",@f_blockPath");
		sb.append(",?");//sb.append(",@f_lenLoc");
		sb.append(",?");//sb.append(",@f_sizeLoc");
		sb.append(",?");//sb.append(",@f_pid");
		sb.append(",?");//sb.append(",@f_fdChild");
		sb.append(")");

		DbHelper db = new DbHelper();
		PreparedStatement cmd = db.GetCommand(sb.toString());
		try {
			cmd.setString (1, inf.id);
			cmd.setBoolean(2, inf.fdTask);
			cmd.setString (3, inf.uid);
			cmd.setString (4, inf.nameLoc);
			cmd.setString (5, inf.nameSvr);
			cmd.setString (6, inf.pathLoc);
			cmd.setString (7, inf.pathSvr);
			cmd.setString (8, inf.pathRel);
			cmd.setInt    (9, inf.blockCount);
			cmd.setInt    (10,inf.blockSize);
			cmd.setString (11,inf.blockPath);
			cmd.setLong   (12,inf.lenLoc);
			cmd.setString (13,inf.sizeLoc);
			cmd.setString(14, inf.pid);
			cmd.setBoolean(15, inf.fdChild);
		} catch (SQLException e) {e.printStackTrace();}
		
		db.ExecuteNonQuery(cmd);
	}
	/**
	 * 增加一条数据，在f_create中调用。
	 * 文件名称，本地路径，远程路径，相对路径都使用原始字符串。
	 * d:\soft\QQ2012.exe
	 * @param f
	 * @return
	 */
	public void Add(FileInf f) throws ParseException, IllegalAccessException, SQLException {
		this.table().insert(f);
	}

	public void complete(String id) throws ParseException, IllegalAccessException, SQLException {
		this.table().update(SqlSeter.build()
						.sql("f_lenSvr=f_lenLoc")
						.set("f_perSvr","100%")
						.set("f_complete",true)
						.set("f_scan",true),
				SqlWhere.build().eq("f_id",id)
		);
	}

	public FileInf read(String id) throws ParseException, IllegalAccessException, SQLException {
		FileInf f = this.table().readOne(FileInf.build(),
			SqlWhere.build().eq("f_id",id));

		return f;
	}
	
	public FileInf read(String pathRel,String id,String uid) throws ParseException, IllegalAccessException, SQLException {
		FileInf f = this.table().readOne(FileInf.build(),"f_id,f_pid,f_pidRoot,f_pathSvr,f_pathRel",
				SqlWhere.build()
						.eq("f_pathRel", pathRel)
						.eq("f_deleted", false)
						.eq("f_uid", uid)
						.ineq("f_id", id)
                );
		return f;
	}

	public FileInf exist_file(String md5) throws ParseException, IllegalAccessException, SQLException {
		FileInf f = this.table().readOne(FileInf.build(),
				up7.sql.SqlWhere.build()
						.eq("f_complete",true)
						.eq("f_md5",md5)
		);

		return f;
	}

	public void remove(String id,String uid) throws ParseException, IllegalAccessException, SQLException {
		this.table().update(
				SqlSeter.build().set("f_deleted",true),
				SqlWhere.build().eq("f_uid",uid)
						.eq("f_id",id)
		);
	}
	
	public String all_uncmp(String uid)
	{
		StringBuilder sb = new StringBuilder();
		sb.append("select ");
		sb.append(" f_id");
		sb.append(",f_fdTask");
		sb.append(",f_nameLoc");
		sb.append(",f_pathLoc");
		sb.append(",f_pathSvr");
		sb.append(",f_blockPath");
		sb.append(",f_sizeLoc");
		sb.append(",f_perSvr");
		sb.append(" from up7_files");
		sb.append(" where f_uid=? and f_deleted=0 and f_complete=0");

		ArrayList<FileInf> files = new ArrayList<FileInf>();
		DbHelper db = new DbHelper();
		PreparedStatement cmd = db.GetCommand(sb.toString());
		try {
			cmd.setString(1, uid);
			ResultSet r = db.ExecuteDataSet(cmd);
			while(r.next())
			{
				FileInf f 		= new FileInf();
				f.id 			= r.getString(1);
				f.fdTask 		= r.getBoolean(2);
				f.nameLoc 		= r.getString(3);
				f.pathLoc 		= r.getString(4);
				f.pathSvr 		= r.getString(5);
				f.blockPath 	= r.getString(6);
				f.sizeLoc 		= r.getString(7);
				f.perSvr 		= r.getString(8);
				files.add(f);
			}
			r.close();
			cmd.getConnection().close();
			cmd.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(files.size() < 1) return "";
		Gson g = new Gson();
	    return g.toJson( files );	
	}
	
	public void process(String id,String perSvr,long lenSvr) throws ParseException, IllegalAccessException, SQLException {
		this.table().update(SqlSeter.build()
						.set("f_lenSvr",lenSvr)
						.set("f_perSvr",perSvr),
				SqlWhere.build().eq("f_id",id)
		);
	}
	
	/// <summary>
	/// 删除一条数据，并不真正删除，只更新删除标识。
	/// </summary>
	/// <param name="f_uid"></param>
	/// <param name="f_id"></param>
	public void Delete(String f_uid,String f_id) throws ParseException, IllegalAccessException, SQLException {
		this.table().update(
				SqlSeter.build().set("f_deleted",true),
				SqlWhere.build().eq("f_uid",f_uid)
				.eq("f_id",f_id)
		);
	}

    public static boolean existSameFile(String n,String pid)
    {
        SqlWhereMerge swm = new SqlWhereMerge();
        swm.equal("f_nameLoc", n.trim());
        swm.equal("f_pid", pid.trim(), false);
        swm.equal("f_deleted", 0);

        SqlExec se = new SqlExec();
        return se.count("up7_files", swm.to_sql())>0;
    }
    
    public void delete(String pathRel,String uid,String id) throws ParseException, IllegalAccessException, SQLException {
		SqlTable.build("up7_files").update(
				SqlSeter.build().set("f_deleted",true),
				SqlWhere.build().eq("f_uid",uid)
				.eq("f_pathRel",pathRel)
				.eq("f_fdTask",false)
				.ineq("f_id",id)
		);
	}
    
    /**
	 * 更新子文件路径
	 * @param pathRelOld
	 * @param pathRelNew
	 */
	public void updatePathRel(String pathRelOld,String pathRelNew)
	{
        //更新子文件路径
        String sql = String.format("update up7_files set f_pathRel=REPLACE(f_pathRel,'%s/','%s/') where CHARINDEX('%s/',f_pathRel)=1",
            pathRelOld,
            pathRelNew,
            pathRelOld
            );
        
        SqlExec se = new SqlExec();
        se.exec(sql);
		
	}

	//批量添加
	public void addBatch(List<FileInf> arr) throws SQLException, ParseException, IllegalAccessException {
		this.table().inserts(arr);
	}
}